<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<TITLE>paper/PracticeQuestions_Solutions_4296_9673</TITLE>
<META http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<META name="generator" content="pdftohtml 0.39">
<META name="author" content="i">
<META name="date" content="2010-03-19T14:19:16+00:00">
</HEAD>
<BODY bgcolor="#A0A0A0" vlink="blue" link="blue">
<!-- Page 1 -->
<a name="1"></a>
<DIV id="div1" style="position:relative;width:892;height:1262;">
<STYLE type="text/css">
<!--
	.ft0{font-size:16px;font-family:Times;color:#000000;}
	.ft1{font-size:16px;font-family:Times;color:#000000;}
	.ft2{font-size:16px;font-family:Helvetica;color:#000000;}
	.ft3{font-size:16px;font-family:Times;color:#000000;}
	.ft4{font-size:16px;line-height:23px;font-family:Times;color:#000000;}
	.ft5{font-size:16px;line-height:23px;font-family:Times;color:#000000;}
-->
</STYLE>
<IMG width="892" height="1262" src="paper/PracticeQuestions_Solutions_4296_9673001.png" alt="background image">
<DIV style="position:absolute;top:109;left:108"><nobr><span id="1" class="ft4"><b>Group 1: True/False Questions <br> </b></span></nobr></DIV>
<DIV style="position:absolute;top:156;left:135"><nobr><span id="2" class="ft1">1.</span></nobr></DIV>
<DIV style="position:absolute;top:156;left:149"><nobr><span id="3" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:156;left:162"><nobr><span id="4" class="ft1">It is not possible to have a clustering and a primary index on the same relation </span></nobr></DIV>
<DIV style="position:absolute;top:180;left:135"><nobr><span id="5" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:204;left:189"><nobr><span id="6" class="ft1">a.</span></nobr></DIV>
<DIV style="position:absolute;top:203;left:201"><nobr><span id="7" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:204;left:216"><nobr><span id="8" class="ft1">True </span></nobr></DIV>
<DIV style="position:absolute;top:227;left:189"><nobr><span id="9" class="ft1">b.</span></nobr></DIV>
<DIV style="position:absolute;top:227;left:203"><nobr><span id="10" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:227;left:216"><nobr><span id="11" class="ft1">False </span></nobr></DIV>
<DIV style="position:absolute;top:251;left:108"><nobr><span id="12" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:275;left:135"><nobr><span id="13" class="ft1">2.</span></nobr></DIV>
<DIV style="position:absolute;top:275;left:149"><nobr><span id="14" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:275;left:162"><nobr><span id="15" class="ft5">In a B+ Tree with order p, the root node can not have fewer pointers than the ceiling <br>of (p/2) </span></nobr></DIV>
<DIV style="position:absolute;top:323;left:135"><nobr><span id="16" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:346;left:189"><nobr><span id="17" class="ft1">a.</span></nobr></DIV>
<DIV style="position:absolute;top:346;left:201"><nobr><span id="18" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:346;left:216"><nobr><span id="19" class="ft1">True </span></nobr></DIV>
<DIV style="position:absolute;top:370;left:189"><nobr><span id="20" class="ft1">b.</span></nobr></DIV>
<DIV style="position:absolute;top:370;left:203"><nobr><span id="21" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:370;left:216"><nobr><span id="22" class="ft1">False </span></nobr></DIV>
<DIV style="position:absolute;top:394;left:108"><nobr><span id="23" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:418;left:135"><nobr><span id="24" class="ft1">3.</span></nobr></DIV>
<DIV style="position:absolute;top:418;left:149"><nobr><span id="25" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:418;left:162"><nobr><span id="26" class="ft1">An index that might be not useful in range queries can be useful in equality queries </span></nobr></DIV>
<DIV style="position:absolute;top:442;left:135"><nobr><span id="27" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:466;left:189"><nobr><span id="28" class="ft1">a.</span></nobr></DIV>
<DIV style="position:absolute;top:465;left:201"><nobr><span id="29" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:466;left:216"><nobr><span id="30" class="ft1">True </span></nobr></DIV>
<DIV style="position:absolute;top:489;left:189"><nobr><span id="31" class="ft1">b.</span></nobr></DIV>
<DIV style="position:absolute;top:489;left:203"><nobr><span id="32" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:489;left:216"><nobr><span id="33" class="ft1">False </span></nobr></DIV>
<DIV style="position:absolute;top:513;left:108"><nobr><span id="34" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:537;left:135"><nobr><span id="35" class="ft1">4.</span></nobr></DIV>
<DIV style="position:absolute;top:537;left:149"><nobr><span id="36" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:537;left:162"><nobr><span id="37" class="ft1">Spanned records must be used in if records size &lt; block size </span></nobr></DIV>
<DIV style="position:absolute;top:561;left:108"><nobr><span id="38" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:585;left:189"><nobr><span id="39" class="ft1">a.</span></nobr></DIV>
<DIV style="position:absolute;top:584;left:201"><nobr><span id="40" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:585;left:216"><nobr><span id="41" class="ft1">True </span></nobr></DIV>
<DIV style="position:absolute;top:608;left:189"><nobr><span id="42" class="ft1">b.</span></nobr></DIV>
<DIV style="position:absolute;top:608;left:203"><nobr><span id="43" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:608;left:216"><nobr><span id="44" class="ft1">False </span></nobr></DIV>
<DIV style="position:absolute;top:632;left:108"><nobr><span id="45" class="ft0"><b> </b></span></nobr></DIV>
<DIV style="position:absolute;top:656;left:135"><nobr><span id="46" class="ft1">5.</span></nobr></DIV>
<DIV style="position:absolute;top:656;left:149"><nobr><span id="47" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:656;left:162"><nobr><span id="48" class="ft1">Consider the following SQL statement:  </span></nobr></DIV>
<DIV style="position:absolute;top:680;left:216"><nobr><span id="49" class="ft3"><i>Select id from Members where Activity='Squash'.  </i></span></nobr></DIV>
<DIV style="position:absolute;top:704;left:135"><nobr><span id="50" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:727;left:162"><nobr><span id="51" class="ft5">Knowing that there are only 9 different types of activities available for members and <br>that there are 50,000 members, I should not consider creating an index on the Activity <br>attribute. <br> </span></nobr></DIV>
<DIV style="position:absolute;top:823;left:189"><nobr><span id="52" class="ft1">a.</span></nobr></DIV>
<DIV style="position:absolute;top:822;left:201"><nobr><span id="53" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:823;left:216"><nobr><span id="54" class="ft1">True </span></nobr></DIV>
<DIV style="position:absolute;top:846;left:189"><nobr><span id="55" class="ft1">b.</span></nobr></DIV>
<DIV style="position:absolute;top:846;left:203"><nobr><span id="56" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:846;left:216"><nobr><span id="57" class="ft1">False   </span></nobr></DIV>
<DIV style="position:absolute;top:870;left:108"><nobr><span id="58" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:894;left:135"><nobr><span id="59" class="ft1">6.</span></nobr></DIV>
<DIV style="position:absolute;top:894;left:149"><nobr><span id="60" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:894;left:162"><nobr><span id="61" class="ft5">Assuming that the two relations S and R are going to be joined over the following <br>condition R.A = S.B where A is a key of the relation R, then the join selectivity of this <br>relation is 1/S </span></nobr></DIV>
<DIV style="position:absolute;top:965;left:135"><nobr><span id="62" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:989;left:189"><nobr><span id="63" class="ft1">a.</span></nobr></DIV>
<DIV style="position:absolute;top:989;left:201"><nobr><span id="64" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:989;left:216"><nobr><span id="65" class="ft1">True </span></nobr></DIV>
<DIV style="position:absolute;top:1013;left:189"><nobr><span id="66" class="ft1">b.</span></nobr></DIV>
<DIV style="position:absolute;top:1013;left:203"><nobr><span id="67" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:1013;left:216"><nobr><span id="68" class="ft1">False </span></nobr></DIV>
<DIV style="position:absolute;top:1037;left:108"><nobr><span id="69" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:1061;left:135"><nobr><span id="70" class="ft1">7.</span></nobr></DIV>
<DIV style="position:absolute;top:1060;left:149"><nobr><span id="71" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:1061;left:162"><nobr><span id="72" class="ft5">The Query optimizer considers the selectivity of an attribute when choosing which <br>index to use  </span></nobr></DIV>
<DIV style="position:absolute;top:1108;left:135"><nobr><span id="73" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:1132;left:189"><nobr><span id="74" class="ft1">a.</span></nobr></DIV>
<DIV style="position:absolute;top:1132;left:201"><nobr><span id="75" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:1132;left:216"><nobr><span id="76" class="ft1">True </span></nobr></DIV>
</DIV>
<!-- Page 2 -->
<a name="2"></a>
<DIV style="position:relative;width:892;height:1262;">
<STYLE type="text/css">
<!--
	.ft6{font-size:14px;font-family:Times;color:#000000;}
-->
</STYLE>
<IMG width="892" height="1262" src="paper/PracticeQuestions_Solutions_4296_9673002.png" alt="background image">
<DIV style="position:absolute;top:108;left:189"><nobr><span id="77" class="ft1">b.</span></nobr></DIV>
<DIV style="position:absolute;top:108;left:203"><nobr><span id="78" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:108;left:216"><nobr><span id="79" class="ft1">False </span></nobr></DIV>
<DIV style="position:absolute;top:132;left:108"><nobr><span id="80" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:156;left:135"><nobr><span id="81" class="ft1">8.</span></nobr></DIV>
<DIV style="position:absolute;top:156;left:149"><nobr><span id="82" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:156;left:162"><nobr><span id="83" class="ft5">When creating a multilevel index, the fan out factor is used repetitively till a <br>maximum of 2 full blocks is reached </span></nobr></DIV>
<DIV style="position:absolute;top:204;left:135"><nobr><span id="84" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:227;left:189"><nobr><span id="85" class="ft1">a.</span></nobr></DIV>
<DIV style="position:absolute;top:227;left:201"><nobr><span id="86" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:227;left:216"><nobr><span id="87" class="ft1">True </span></nobr></DIV>
<DIV style="position:absolute;top:251;left:189"><nobr><span id="88" class="ft1">b.</span></nobr></DIV>
<DIV style="position:absolute;top:251;left:203"><nobr><span id="89" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:251;left:216"><nobr><span id="90" class="ft1">False </span></nobr></DIV>
<DIV style="position:absolute;top:275;left:108"><nobr><span id="91" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:299;left:135"><nobr><span id="92" class="ft1">9.</span></nobr></DIV>
<DIV style="position:absolute;top:299;left:149"><nobr><span id="93" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:299;left:162"><nobr><span id="94" class="ft5">The balance of the B+Trees comes from the constraint of having their nodes either <br>full or half full at all times; however, such balance doesn't relate to their efficiency <br>when used to implement indexes </span></nobr></DIV>
<DIV style="position:absolute;top:370;left:135"><nobr><span id="95" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:394;left:189"><nobr><span id="96" class="ft1">a.</span></nobr></DIV>
<DIV style="position:absolute;top:394;left:201"><nobr><span id="97" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:394;left:216"><nobr><span id="98" class="ft1">True </span></nobr></DIV>
<DIV style="position:absolute;top:418;left:189"><nobr><span id="99" class="ft1">b.</span></nobr></DIV>
<DIV style="position:absolute;top:418;left:203"><nobr><span id="100" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:418;left:216"><nobr><span id="101" class="ft1">False </span></nobr></DIV>
<DIV style="position:absolute;top:442;left:108"><nobr><span id="102" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:466;left:135"><nobr><span id="103" class="ft1">10.</span></nobr></DIV>
<DIV style="position:absolute;top:465;left:158"><nobr><span id="104" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:466;left:162"><nobr><span id="105" class="ft5">In the nested loop approach, when calculating the cost of a JOIN, it is always better to <br>use the relation with the smaller number of blocks as the outer loop. </span></nobr></DIV>
<DIV style="position:absolute;top:513;left:135"><nobr><span id="106" class="ft1"> </span></nobr></DIV>
<DIV style="position:absolute;top:537;left:189"><nobr><span id="107" class="ft1">a.</span></nobr></DIV>
<DIV style="position:absolute;top:537;left:201"><nobr><span id="108" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:537;left:216"><nobr><span id="109" class="ft1">True </span></nobr></DIV>
<DIV style="position:absolute;top:561;left:189"><nobr><span id="110" class="ft1">b.</span></nobr></DIV>
<DIV style="position:absolute;top:561;left:203"><nobr><span id="111" class="ft2"> </span></nobr></DIV>
<DIV style="position:absolute;top:561;left:216"><nobr><span id="112" class="ft1">False </span></nobr></DIV>
<DIV style="position:absolute;top:587;left:108"><nobr><span id="113" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:626;left:108"><nobr><span id="114" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:664;left:108"><nobr><span id="115" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:702;left:108"><nobr><span id="116" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:740;left:108"><nobr><span id="117" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:778;left:108"><nobr><span id="118" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:816;left:108"><nobr><span id="119" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:855;left:108"><nobr><span id="120" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:893;left:108"><nobr><span id="121" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:931;left:108"><nobr><span id="122" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:969;left:108"><nobr><span id="123" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:1007;left:108"><nobr><span id="124" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:1045;left:108"><nobr><span id="125" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:1084;left:108"><nobr><span id="126" class="ft6"> </span></nobr></DIV>
<DIV style="position:absolute;top:1122;left:108"><nobr><span id="127" class="ft6"> </span></nobr></DIV>
</DIV>
<!-- Page 3 -->
<a name="3"></a>
<DIV style="position:relative;width:892;height:1262;">
<STYLE type="text/css">
<!--
	.ft7{font-size:14px;font-family:Times;color:#000000;}
	.ft8{font-size:16px;line-height:23px;font-family:Times;color:#000000;}
-->
</STYLE>
<IMG width="892" height="1262" src="paper/PracticeQuestions_Solutions_4296_9673003.png" alt="background image">
<DIV style="position:absolute;top:109;left:108"><nobr><span id="128" class="ft0"><b>Group 2: Heuristic Optimization </b></span></nobr></DIV>
<DIV style="position:absolute;top:147;left:108"><nobr><span id="129" class="ft8"><i>Select SName <br>From Student S1, Enroll E, Section S2, Course C, Dept D <br>Where S1.SId=E.StudentID and E.SectionID=S2.SectID and C.DeptID=D.DId and <br>C.Cid=E.CourseID and E.Grade='F' and S2.YearOffered=2004 and D.DName='Math' <br></i> </span></nobr></DIV>
<DIV style="position:absolute;top:281;left:108"><nobr><span id="130" class="ft5">(a) Convert this SQL statement into a relational algebra expression then map it into an initial <br>query tree (without considering any optimization rules) </span></nobr></DIV>
<DIV style="position:absolute;top:344;left:108"><nobr><span id="131" class="ft5">(b) Use Heuristic optimization rules to transform your initial tree into an optimized Query <br>Tree. Make sure you point out all the steps you made and the rules you used to transform the <br>initial tree into the optimized one  </span></nobr></DIV>
<DIV style="position:absolute;top:848;left:668"><nobr><span id="132" class="ft7"><b> </b></span></nobr></DIV>
<DIV style="position:absolute;top:882;left:108"><nobr><span id="133" class="ft7"><b> </b></span></nobr></DIV>
<DIV style="position:absolute;top:920;left:108"><nobr><span id="134" class="ft7"><b> </b></span></nobr></DIV>
<DIV style="position:absolute;top:958;left:108"><nobr><span id="135" class="ft7"><b> </b></span></nobr></DIV>
<DIV style="position:absolute;top:997;left:108"><nobr><span id="136" class="ft6"> </span></nobr></DIV>
</DIV>
</BODY>
</HTML>
